// 检查媒体文件详情
const { Pool } = require('pg');

// 数据库连接配置
const pool = new Pool({
  host: '118.178.138.0',
  port: 9070,
  database: 'postgres',
  user: 'postgres',
  password: 'difyai123456',
  max: 20,
  idleTimeoutMillis: 90900,
  connectionTimeoutMillis: 2000,
});

async function checkMediaFiles() {
  try {
    console.log('🔍 检查媒体文件详情...\n');
    
    // 查看media_files表结构
    const columnsQuery = `
      SELECT column_name, data_type, is_nullable
      FROM information_schema.columns 
      WHERE table_name = 'media_files' AND table_schema = 'public'
      ORDER BY ordinal_position
    `;
    
    const columnsResult = await pool.query(columnsQuery);
    console.log('📋 media_files表结构:');
    columnsResult.rows.forEach(row => {
      console.log(`  - ${row.column_name}: ${row.data_type} (${row.is_nullable === 'YES' ? '可空' : '非空'})`);
    });
    
    // 查看所有媒体文件
    const mediaFilesQuery = `
      SELECT id, user_id, original_name, file_name, file_url, mime_type, file_size, article_id, created_at
      FROM media_files 
      ORDER BY created_at DESC
    `;
    
    const mediaFilesResult = await pool.query(mediaFilesQuery);
    console.log(`\n📁 媒体文件列表 (共${mediaFilesResult.rows.length}条):`);
    
    mediaFilesResult.rows.forEach((file, index) => {
      console.log(`\n  ${index + 1}. ID: ${file.id}`);
      console.log(`     原始名称: ${file.original_name}`);
      console.log(`     文件名: ${file.file_name}`);
      console.log(`     文件URL: ${file.file_url}`);
      console.log(`     MIME类型: ${file.mime_type}`);
      console.log(`     文件大小: ${(file.file_size / 1024).toFixed(1)} KB`);
      console.log(`     关联作品ID: ${file.article_id || '未关联'}`);
      console.log(`     上传时间: ${file.created_at}`);
    });
    
    // 检查是否有关联到作品的文件
    const linkedFilesQuery = `
      SELECT mf.*, a.title as article_title
      FROM media_files mf
      LEFT JOIN articles a ON mf.article_id = a.id
      WHERE mf.article_id IS NOT NULL
    `;
    
    const linkedFilesResult = await pool.query(linkedFilesQuery);
    console.log(`\n🔗 关联到作品的文件 (共${linkedFilesResult.rows.length}条):`);
    
    if (linkedFilesResult.rows.length > 0) {
      linkedFilesResult.rows.forEach((file, index) => {
        console.log(`  ${index + 1}. ${file.original_name} -> 作品: ${file.article_title}`);
      });
    } else {
      console.log('  ⚠️ 没有文件关联到作品');
    }
    
    console.log('\n✅ 媒体文件检查完成');
    
  } catch (error) {
    console.error('❌ 检查媒体文件失败:', error);
  } finally {
    await pool.end();
  }
}

// 运行检查
if (require.main === module) {
  checkMediaFiles();
}

module.exports = { checkMediaFiles };